﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Reflection;

namespace MySql.Simple.Mapper.Structures
{
    /// <summary>
    /// Represents a collection of columns for a table
    /// </summary>
    public class ColumnCollection : List<Column>
    {
        /// <summary>
        /// Geneates the SQL need to create the table.
        /// </summary>
        /// <returns></returns>
        public string ToCreateTableSql()
        {
            var sql = string.Empty;

            var primaryKey = string.Empty;

            foreach(var column in this)
            {
                if (!string.IsNullOrEmpty(sql)) sql += ", ";

                sql += column.ToDefinitionSql();

                if (column.PrimaryKey) primaryKey = column.Name;
            }

            if (!string.IsNullOrEmpty(primaryKey)) sql += string.Format(", PRIMARY KEY (`{0}`)", primaryKey);

            return sql;
        }

        /// <summary>
        /// Generates an SQL fragment to select all columns from a table.
        /// </summary>
        /// <returns></returns>
        public string ToSelectColumnsSql()
        {
            var sql = string.Empty;

            foreach (var column in this)
            {
                if (!string.IsNullOrEmpty(sql)) sql += ", ";

                sql += "`" + column.Name + "`";
            }

            return sql;
        }

        /// <summary>
        /// Generates an SQL fragment to insert all columns into a table.
        /// </summary>
        /// <returns></returns>
        public string ToInsertColumnsSql()
        {
            var sql = string.Empty;

            foreach (var column in this)
            {
                // Don't add auto-incrementing primary keys
                if (column.PrimaryKey & column.AutoIncrement) continue;

                // don't add null values
                if (column.Value == null) continue;

                if (!string.IsNullOrEmpty(sql)) sql += ", ";

                sql += "`" + column.Name + "`";
            }

            return sql;
        }

        /// <summary>
        /// Generates an SQL fragment to insert all columns as parameters into a table.
        /// </summary>
        /// <returns></returns>
        public string ToInsertParametersSql()
        {
            var sql = string.Empty;

            foreach (var column in this)
            {
                // Don't add auto-incrementing primary keys
                if (column.PrimaryKey & column.AutoIncrement) continue;

                // don't add null values
                if (column.Value == null) continue;

                if (!string.IsNullOrEmpty(sql)) sql += ", ";

                sql += "?" + column.Name;
            }

            return sql;
        }


        /// <summary>
        /// Gets the name of the primary key.
        /// </summary>
        /// <value>The name of the primary key.</value>
        public string PrimaryKeyName
        {
            get
            {
                var primaryKeyName = string.Empty;

                foreach(var column in this)
                {
                    if (!column.PrimaryKey) continue;

                    primaryKeyName = column.Name;

                    break;
                }

                return primaryKeyName;
            }
        }

        /// <summary>
        /// Gets a value indicating whether this instance has a primary key set.
        /// </summary>
        /// <value>
        /// 	<c>true</c> if this instance has primary key; otherwise, <c>false</c>.
        /// </value>
        public bool HasPrimaryKey
        {
            get { return string.IsNullOrEmpty(PrimaryKeyName); }
        }

        /// <summary>
        /// Gets the value of the primary key column.
        /// </summary>
        /// <value>The primary key value.</value>
        public object PrimaryKeyValue
        {
            get
            {
                object primaryKey = null;

                foreach (var column in this)
                {
                    if (!column.PrimaryKey) continue;

                    primaryKey = column.Value;

                    break;
                }

                return primaryKey;
            }
        }

        /// <summary>
        /// Returns this collection as a list of <see cref="DbParameter"/>s.
        /// </summary>
        /// <returns></returns>
        public IList<DbParameter> ToDbParameters()
        {
            var parameters = new List<DbParameter>();

            foreach(var column in this)
            {
                // Don't include PK if auto-incrementing
                if (column.PrimaryKey && column.AutoIncrement) continue;

                parameters.Add(column.ToDbParameter());
            }


            return parameters;
        }

        /// <summary>
        /// Generates an SQL fragment to update all columns as parameters into a table.
        /// </summary>
        /// <returns></returns>
        public object ToUpdateColumnsSql()
        {
            var sql = string.Empty;

            foreach (var column in this)
            {
                // Don't update auto-incrementing primary keys
                if (column.PrimaryKey & column.AutoIncrement) continue;

                // Don't update null values
                if (column.Value == null) continue;

                if (!string.IsNullOrEmpty(sql)) sql += ", ";

                sql += "`" + column.Name + "`";
                sql += " = ?" + column.Name;
            }

            return sql;
        }

        public Column Find(PropertyInfo propertyInfo)
        {
            return Find(propertyInfo.Name);
        }

        /// <summary>
        /// Finds the column with the specified property name.
        /// </summary>
        /// <param name="propertyName">Name of the property.</param>
        /// <returns></returns>
        public Column Find(string propertyName)
        {
            foreach (var column in this)
            {
                if (column.PropertyName == propertyName)
                {
                    return column;
                }
            }

            return null;
        }
    }
}
